package com.qf.java2106.huangshengrui.hotel.dao.impl;

import com.qf.java2106.huangshengrui.hotel.dao.IFoodDao;
import com.qf.java2106.huangshengrui.hotel.pojos.Food;
import com.qf.java2106.huangshengrui.hotel.utils.JdbcUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @author q
 * @version V1.0
 * @date 2021/10/26
 **/
public class FoodDaoImpl implements IFoodDao {

    QueryRunner queryRunner = null;

    @Override
    public List<Food> findFoodLikeKey(String keyword) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT food_id foodId,type_id typeId,food_name foodName,food_price foodPrice,food_mprice foodVipPrice,food_image foodImage,food_desc foodDesc FROM t_food WHERE food_name LIKE ?";
        return queryRunner.query(JdbcUtils.getConnection(),sql,new BeanListHandler<>(Food.class), keyword);
    }

    @Override
    public Food findFoodById(Integer id) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT food_id foodId,type_id typeId,food_name foodName,food_price foodPrice,food_mprice foodVipPrice,food_image foodImage,food_desc foodDesc FROM t_food WHERE food_id = ?";
        return queryRunner.query(JdbcUtils.getConnection(),sql,new BeanHandler<>(Food.class),id);
    }

    @Override
    public Integer saveFood(Food food) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "INSERT INTO t_food (type_id,food_name,food_price,food_mprice,food_image,food_desc) VALUE (?,?,?,?,?,?)";
        return queryRunner.update(JdbcUtils.getConnection(),sql,food.getTypeId(),food.getFoodName(),food.getFoodPrice(),food.getFoodVipPrice(),food.getFoodImage(),food.getFoodDesc());
    }

    @Override
    public Integer findFoodByName(String foodName) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "SELECT food_id FROM t_food WHERE food_name = ?";
        return queryRunner.query(JdbcUtils.getConnection(),sql, new ScalarHandler<>(),foodName);
    }

    @Override
    public Integer updateFood(Food food) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "UPDATE t_food SET type_id = ?,food_name = ?,food_price = ?,food_mprice = ?,food_image = ?,food_desc = ? WHERE food_id = ?";
        return queryRunner.update(JdbcUtils.getConnection(),sql,food.getTypeId(),food.getFoodName(),food.getFoodPrice(),food.getFoodVipPrice(),food.getFoodImage(),food.getFoodDesc(),food.getFoodId());
    }

    @Override
    public Integer deleteFood(Integer id) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        String sql = "DELETE FROM t_food WHERE food_id = ?";
        return queryRunner.update(JdbcUtils.getConnection(),sql,id);
    }

    @Override
    public List<Food> findPageFood(Integer start, Integer pageSize, Integer typeId, String foodName) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        List<Object> params = new ArrayList();
        StringBuilder sql = new StringBuilder("SELECT food_id foodId,type_id typeId,food_name foodName,food_price foodPrice,food_mprice foodVipPrice,food_image foodImage,food_desc foodDesc " +
                "FROM t_food " +
                "WHERE 1=1 ");
        /*if (typeId != -1){
            sql.append(" AND type_id="+typeId );
        }
        sql.append(" AND food_name LIKE "+ " '"+ foodName + "' ");
        */
        if (typeId != -1){
            sql.append(" AND type_id = ? " );
            params.add(typeId);
        }
        sql.append(" AND food_name LIKE ? ");
        params.add(foodName);
        sql.append(" LIMIT ?,? ") ;
        params.add(start);
        params.add(pageSize);
        return queryRunner.query(JdbcUtils.getConnection(),sql.toString(),new BeanListHandler<>(Food.class),params.toArray());
    }

    @Override
    public Long findTotalCounts(Integer typeId, String foodName) throws SQLException {
        queryRunner = new QueryRunner(JdbcUtils.getDataSource());
        StringBuilder sql = new StringBuilder("SELECT COUNT(1) FROM t_food WHERE 1=1 ");
        if (typeId != -1){
            sql.append(" AND type_id="+typeId );
        }
        if (null != foodName){
            sql.append(" AND food_name LIKE "+ " '"+ foodName + "' ");
        }
        String sqlStr = sql.toString();
        return queryRunner.query(JdbcUtils.getConnection(),sqlStr, new ScalarHandler<>());
    }

}
